package com.monster.dao;

import com.monster.pojo.Album;
import com.monster.pojo.Playlist;
import com.monster.pojo.Singer;
import com.monster.pojo.Song;
import com.monster.pojo.User;
import com.monster.util.PageUtil;

import java.sql.SQLException;
import java.util.ArrayList;

/**
 * User的数据访问层实现类，包含查询user表和相关联的其他表得到与user相关的数据的方法
 * @author cyb
 */
public class UserDaoImpl extends BaseDao implements UserDao {

    /**
     * 根据id查询user表得到用户信息
     * @param id
     * @return User对象
     */
    @Override
    public User getUserById(Long id) {
        User user = new User();
        String sql = "SELECT * FROM user WHERE user_id=?";
        Object[] obj = {id};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                user.setId(id);
                user.setName(rs.getString(2));
                user.setPhoneNumber(rs.getString(3));
                user.setPassword(rs.getString(4));
                user.setHead(rs.getString(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 根据user_id和focus_type=1联表分页查询得到关注的singer集合
     * @param userId
     * @param page 页号
     * @return Singer集合
     */
    @Override
    public ArrayList<Singer> listFocusSingesByUserId(Long userId, int page) {
        ArrayList<Singer> singers = new ArrayList<>();
        String sql = "SELECT singer_id,singer_name,singer_poster,singer_hot,singer_sex FROM singer s " +
                "LEFT JOIN user_focus uf ON s.singer_id=uf.focus_id " +
                "LEFT JOIN USER u ON u.user_id=uf.user_id " +
                "WHERE u.user_id=? AND focus_type=1 ORDER BY uf_id DESC LIMIT ?,20";
        PageUtil.sql = sql;
        Object[] obj = {userId, (page-1)*20};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                Singer singer = new Singer();
                singer.setSingerId(rs.getLong(1));
                singer.setSingerName(rs.getString(2));
                singer.setPoster(rs.getString(3));
                singer.setHot(rs.getInt(4));
                singer.setSex(rs.getString(5));
                singers.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singers;
    }

    /**
     * 根据user_id和focus_type=2联表分页查询得到关注的user集合
     * @param userId 用户Id
     * @param page 页码
     * @return User集合
     */
    @Override
    public ArrayList<User> listFocusUsersByUserId(Long userId, int page) {
        ArrayList<User> users = new ArrayList<>();
        String sql = "SELECT u1.user_id,u1.user_name,u1.user_head FROM USER u1 " +
                "LEFT JOIN user_focus uf ON u1.user_id=uf.focus_id " +
                "LEFT JOIN USER u2 ON u2.user_id=uf.user_id " +
                "WHERE u2.user_id=? AND focus_type=2 ORDER BY uf_id DESC LIMIT ?,20";
        PageUtil.sql = sql;
        Object[] obj = {userId, (page-1)*20};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong(1));
                user.setName(rs.getString(2));
                user.setHead(rs.getString(3));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    /**
     * 根据user_id和like_type=1联表分页查询得到我喜欢的song集合
     * @param userId
     * @param page 页码
     * @return Song集合
     */
    @Override
    public ArrayList<Song> listLikeSongsByUserId(Long userId, int page) {
        ArrayList<Song> songs = new ArrayList<>();
        String sql = "SELECT song_id,song_name,song_time,song_url,song_poster FROM song s " +
                "LEFT JOIN user_like ul ON s.song_id=ul.like_id " +
                "LEFT JOIN USER u ON u.user_id=ul.user_id " +
                "WHERE like_type=1 AND ul.user_id=? ORDER BY ul_id DESC LIMIT ?,20";
        PageUtil.sql = sql;
        Object[] obj = {userId, (page-1)*20};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                Song song = new Song();
                song.setSongId(rs.getLong(1));
                song.setSongName(rs.getString(2));
                song.setTime(rs.getString(3));
                song.setUrl(rs.getString(4));
                song.setPoster(rs.getString(5));
                songs.add(song);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return songs;
    }

    /**
     * 根据user_id和like_type=2联表分页查询得到我喜欢的playlist集合
     * @param userId 用户id
     * @param page 页码
     * @return Playlist集合
     */
    @Override
    public ArrayList<Playlist> listLikePlaylistsByUserId(Long userId, int page) {
        ArrayList<Playlist> playlists = new ArrayList<>();
        String sql = "SELECT playlist_id,playlist_name,playlist_poster,playlist_playcount FROM playlist p " +
                "LEFT JOIN user_like ul ON p.playlist_id=ul.like_id " +
                "LEFT JOIN USER u ON u.user_id=ul.user_id " +
                "WHERE like_type=2 AND ul.user_id=? ORDER BY ul_id DESC LIMIT ?,10";
        PageUtil.sql = sql;
        Object[] obj = {userId, (page-1)*10};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                Playlist p = new Playlist();
                p.setPlaylistId(rs.getLong(1));
                p.setPlaylistName(rs.getString(2));
                p.setPlaylistPoster(rs.getString(3));
                p.setPlaylistPlaycount(rs.getLong(4));
                playlists.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return playlists;
    }

    /**
     * 删除user_like表中指定Id的收藏信息
     * @param userLikeId ul_id
     * @return sql语句影响行数
     */
    @Override
    public int deleteUserLike(Long userLikeId) {
        String sql = "DELETE FROM user_like WHERE ul_id=?";
        Object[] obj = {userLikeId};
        return this.executeUpdate(sql, obj);
    }

    /**
     * 向user_like表中添加收藏信息
     * @param userId 用户Id
     * @param likeType 收藏类型，其中1为song，2为playlist，3为album
     * @param likeId 收藏的id，表示likeType类型对应的Id
     * @return sql语句影响行数
     */
    @Override
    public int addUserLike(Long userId, int likeType, Long likeId) {
        String sql = "INSERT INTO user_like VALUES (default,?,?,?)";
        Object[] obj = {userId, likeType, likeId};
        return this.executeUpdate(sql, obj);
    }

    /**
     * 向user_focus表中添加关注信息
     * @param userId 用户Id
     * @param focusType 关注类型，其中1为singer，2为user
     * @param focusId 关注的id，表示focusType类型对应的Id
     * @return sql语句影响行数
     */
    @Override
    public int addUserFocus(Long userId, int focusType, Long focusId) {
        String sql = "INSERT INTO user_focus VALUES (default,?,?,?)";
        Object[] obj = {userId, focusType, focusId};
        return this.executeUpdate(sql, obj);
    }

    /**
     * 删除user_focus表中指定Id的收藏信息
     * @param userFocusId uf_id
     * @return sql语句影响行数
     */
    @Override
    public int deleteUserFocus(Long userFocusId) {
        String sql = "DELETE FROM user_focus WHERE uf_id=?";
        Object[] obj = {userFocusId};
        return this.executeUpdate(sql, obj);
    }

    /**
     * 根据user_id和like_type=3联表分页查询得到我喜欢的album集合
     * @param userId 用户id
     * @param page 页码
     * @return Album集合
     */
    @Override
    public ArrayList<Album> listLikeAlbumsByUserId(Long userId, int page) {
        ArrayList<Album> albums = new ArrayList<>();
        String sql = "SELECT album_id,album_name,album_poster,album_public_time FROM album a " +
                "LEFT JOIN user_like ul ON a.album_id=ul.like_id " +
                "LEFT JOIN USER u ON u.user_id=ul.user_id " +
                "WHERE like_type=3 AND ul.user_id=? ORDER BY ul_id DESC LIMIT ?,10";
        PageUtil.sql = sql;
        Object[] obj = {userId, (page-1)*10};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                Album album = new Album();
                album.setAlbumId(rs.getLong(1));
                album.setAlbumName(rs.getString(2));
                album.setAlbumPoster(rs.getString(3));
                album.setAlbumPublicTime(rs.getString(4));
                albums.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return albums;
    }

    /**
     * 根据focus_type=2和focus_id联表分页查询得到粉丝user集合
     * @param userId
     * @param page 页码
     * @return User集合
     */
    @Override
    public ArrayList<User> listFansByUserId(Long userId, int page) {
        ArrayList<User> users = new ArrayList<>();
        String sql = "SELECT u1.user_id,u1.user_name,u1.user_head FROM `USER` u1 LEFT JOIN user_focus uf ON u1.user_id=uf.user_id WHERE focus_id=? AND focus_type=2 ORDER BY uf_id DESC LIMIT ?,20";
        PageUtil.sql = sql;
        Object[] obj = {userId, (page-1)*20};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong(1));
                user.setName(rs.getString(2));
                user.setHead(rs.getString(3));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    /**
     * 根据user_id联表查询得到关注数
     * @param userId
     * @return int 关注数
     */
    @Override
    public int getFollowCountByUserId(Long userId) {
        String sql = "SELECT COUNT(user_id) FROM user_focus WHERE user_id=? AND focus_type=2";
        Object[] obj = {userId};
        rs = executeQuery(sql, obj);
        int result = 0;
        try {
            while (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据user_id联表查询得到粉丝数
     * @param userId
     * @return int 粉丝数
     */
    @Override
    public int getFansCountByUserId(Long userId) {
        String sql = "SELECT COUNT(focus_id) FROM user_focus WHERE focus_id=? AND focus_type=2";
        Object[] obj = {userId};
        rs = executeQuery(sql, obj);
        int result = 0;
        try {
            while (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 添加用户
     * @param user
     * @return sql语句执行后的影响行数
     */
    @Override
    public int addUser(User user) {
        String sql = "INSERT INTO user VALUES(default,?,?,?,?)";
        Object[] obj = {user.getName(), user.getPhoneNumber(), user.getPassword(), user.getHead()};
        return executeUpdate(sql, obj);
    }

    /**
     * 修改用户
     * @param user
     * @return sql语句执行后的影响行数
     */
    @Override
    public int updateUser(User user) {
        String sql = "UPDATE user SET user_name=?,user_phonenumber=?,user_password=?,user_head=? WHERE user_id=?";
        Object[] obj = {user.getName(), user.getPhoneNumber(), user.getPassword(), user.getHead(), user.getId()};
        return executeUpdate(sql, obj);
    }

    /**
     * 根据关键字模糊查询用户信息，按粉丝数降序
     * @param key
     * @return
     */
    @Override
    public ArrayList<User> listUsersByFuzzy(String key) {
        ArrayList<User> users = new ArrayList<>();
        String sql = "SELECT u.user_id,user_name,user_head, COUNT(focus_id) fans FROM user_focus,USER u " +
                "WHERE focus_id=u.user_id AND focus_type=2 " +
                "GROUP BY u.user_id HAVING u.user_id IN (SELECT user_id FROM USER WHERE user_name LIKE ?) " +
                "ORDER BY fans DESC";
        Object[] obj = {"%"+key+"%"};
        rs = executeQuery(sql, obj);
        try {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong(1));
                user.setName(rs.getString(2));
                user.setHead(rs.getString(3));
                user.setFans(rs.getInt(4));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    /**
     * 根据user_id查询user_play表得到听歌列表song的id集合
     * @param userId
     * @return songid集合
     */
    @Override
    public ArrayList<Long> listPlaySongsByUserId(Long userId) {
        ArrayList<Long> songsId = new ArrayList<>();
        String sql = "SELECT song_id FROM user_play WHERE user_id=?";
        Object[] obj = {userId};
        rs = executeQuery(sql, obj);
        try {
            while (rs.next()) {
                songsId.add(rs.getLong(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return songsId;
    }

    /**
     * 在user_play中添加user_id和song_id的对应关系
     * @param userId
     * @param songId
     * @return sql语句影响行数
     */
    @Override
    public int addPlaySong(Long userId, Long songId) {
        String sql = "INSERT INTO user_play VALUES(default,?,?)";
        Object[] obj = {userId, songId};
        return executeUpdate(sql, obj);
    }

    /**
     * 从user_play中删除user_id和song_id的对应关系
     * @param userId
     * @param songId
     * @return sql语句影响行数
     */
    @Override
    public int deletePlaySong(Long userId, Long songId) {
        String sql = "DELETE FROM user_play WHERE user_id=? AND song_id=?";
        Object[] obj = {userId, songId};
        return executeUpdate(sql, obj);
    }

    /**
     * 查询user表获得手机号为特定号码的用户数量
     * @param phoneNumber 手机号
     * @return 用户id
     */
    @Override
    public Long getUserIdByPhone(String phoneNumber) {
        String sql = "SELECT user_id FROM user WHERE user_phonenumber=?";
        Object[] obj = {phoneNumber};
        rs = executeQuery(sql, obj);
        Long userId = null;
        try {
            while (rs.next()) {
                userId = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userId;
    }

    /**
     * 查询user表获得手机号和密码为特定的用户id
     * @param phoneNumber 手机号
     * @param password 密码
     * @return 用户id
     */
    @Override
    public Long getUserIdByPhoneAndPassword(String phoneNumber, String password) {
        String sql = "SELECT user_id FROM user WHERE user_phonenumber=? AND user_password=?";
        Object[] obj = {phoneNumber, password};
        rs = executeQuery(sql, obj);
        Long userId = null;
        try {
            while (rs.next()) {
                userId = rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userId;
    }


}
